﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using Zyg.Utility.DBUtility;
using Zyg.SSO.Core.Business;

namespace Zyg.SSO.Core.Providers.DALProviders
{
    public partial class MySqlProvider : DALProvider
    {
        #region Sql Statements

        private static readonly string SqlSelectAccountAppById = "SELECT `ID`,`AccountID`,`AppID`,`DateCreated`,`ExpireTime` FROM `AccountApp` WHERE `ID` = @ID; set names utf8;";
        private static readonly string SqlSelectAccountAppByAccountIdAndAppId = "SELECT `ID`,`AccountID`,`AppID`,`DateCreated`,`ExpireTime` FROM `AccountApp` WHERE `AccountID` = @AccountId and `AppID` = @AppId; set names utf8;";

        private static readonly string SqlSelectAccountAppsByAccountId = "SELECT `ID`,`AccountID`,`AppID`,`DateCreated`,`ExpireTime` FROM `AccountApp` WHERE `AccountID` = @AccountID; set names utf8;";
        private static readonly string SqlSelectAccountAppsByAppId = "SELECT `ID`,`AccountID`,`AppID`,`DateCreated`,`ExpireTime` FROM `AccountApp` WHERE `AppID` = @AppID; set names utf8;";

        // private static readonly string SqlInsertAccountApp = "IF EXISTS (SELECT null FROM `AccountApp` WHERE `AccountID` = @AccountID AND `AppID` = @AppID) return; ELSE BEGIN INSERT INTO `AccountApp` (`ID`,`AccountID`,`AppID`,`DateCreated`,`ExpireTime`) VALUES (@ID,@AccountID,@AppID,@DateCreated,@ExpireTime); END;set names utf8;";
        private static readonly string SqlInsertAccountApp = "INSERT INTO `AccountApp` (`ID`,`AccountID`,`AppID`,`DateCreated`,`ExpireTime`) VALUES (@ID,@AccountID,@AppID,@DateCreated,@ExpireTime);set names utf8;";

        private static readonly string SqlUpdateAccountAppById = "UPDATE `AccountApp` SET `AccountID` = @AccountID,`AppID` = @AppID,`DateCreated` = @DateCreated,`ExpireTime` = @ExpireTime WHERE `ID` = @ID; set names utf8;";

        private static readonly string SqlDeleteAccountAppById = "DELETE FROM `AccountApp` WHERE `ID` = @ID; set names utf8;";
        private static readonly string SqlDeleteAccountAppsByAccountId = "DELETE FROM `AccountApp` WHERE `AccountID` = @AccountID; set names utf8;";
        private static readonly string SqlDeleteAccountAppsByAppId = "DELETE FROM `AccountApp` WHERE `AppID` = @AppID; set names utf8;";

        #endregion

        public override Zyg.SSO.Core.Business.AccountApp SelectAccountApp(Guid id)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, id);
            MySqlDataReader reader = db.ExecuteSqlReader(SqlSelectAccountAppById);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Business.AccountApp accountApp = new AccountApp();

                if (!reader.IsDBNull(1)) accountApp.AccountID = reader.GetGuid(1);
                if (!reader.IsDBNull(2)) accountApp.AppID = reader.GetGuid(2);
                if (!reader.IsDBNull(3)) accountApp.DateCreated = reader.GetDateTime(3);
                if (!reader.IsDBNull(4)) accountApp.ExpireTime = reader.GetDateTime(4);

                reader.Close();

                return accountApp;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public override Zyg.SSO.Core.Business.AccountApp SelectAccountApp(Guid accountId, Guid appId)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@AccountId", MySqlDbType.VarChar, accountId.ToString());
            db.AddParameter("@AppId", MySqlDbType.VarChar, appId.ToString());
            MySqlDataReader reader = db.ExecuteSqlReader(SqlSelectAccountAppByAccountIdAndAppId);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Business.AccountApp accountApp = new AccountApp();

                if (!reader.IsDBNull(0)) accountApp.Id = reader.GetGuid(0);
                if (!reader.IsDBNull(1)) accountApp.AccountID = reader.GetGuid(1);
                if (!reader.IsDBNull(2)) accountApp.AppID = reader.GetGuid(2);
                if (!reader.IsDBNull(3)) accountApp.DateCreated = reader.GetDateTime(3);
                if (!reader.IsDBNull(4)) accountApp.ExpireTime = reader.GetDateTime(4);

                reader.Close();

                accountApp.MarkOld();

                return accountApp;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public override void InsertAccountApp(Zyg.SSO.Core.Business.AccountApp accountApp)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, accountApp.Id);
            db.AddParameter("@AccountID", MySqlDbType.VarChar, accountApp.AccountID);
            db.AddParameter("@AppID", MySqlDbType.VarChar, accountApp.AppID);
            db.AddParameter("@DateCreated", MySqlDbType.DateTime, accountApp.DateCreated);
            db.AddParameter("@ExpireTime", MySqlDbType.DateTime, accountApp.ExpireTime);

            db.ExecuteSql(SqlInsertAccountApp);
        }

        public override void DeleteAccountApp(Zyg.SSO.Core.Business.AccountApp accountApp)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, accountApp.Id);

            db.ExecuteSql(SqlDeleteAccountAppById);
        }

        public override void DeleteAccountApps(Zyg.SSO.Core.Business.Account account)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@AccountID", MySqlDbType.VarChar, account.Id.ToString());

            db.ExecuteSql(SqlDeleteAccountAppsByAccountId);
        }

        public override void DeleteAccountApps(Zyg.SSO.Core.Business.App app)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@AppID", MySqlDbType.VarChar, app.Id.ToString());

            db.ExecuteSql(SqlDeleteAccountAppsByAppId);
        }

        public override void UpdateAccountApp(Zyg.SSO.Core.Business.AccountApp accountApp)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, accountApp.Id);
            db.AddParameter("@AccountID", MySqlDbType.VarChar, accountApp.AccountID);
            db.AddParameter("@AppID", MySqlDbType.VarChar, accountApp.AppID);
            db.AddParameter("@DateCreated", MySqlDbType.DateTime, accountApp.DateCreated);
            db.AddParameter("@ExpireTime", MySqlDbType.DateTime, accountApp.ExpireTime);

            db.ExecuteSql(SqlUpdateAccountAppById);
        }

        public override void DeleteUpdateAccountApps(Zyg.SSO.Core.Business.Account account, IList<Zyg.SSO.Core.Business.AccountApp> newRelations)
        {
            DeleteUpdateAccountApps(account, null, newRelations);
        }

        public override void DeleteUpdateAccountApps(Zyg.SSO.Core.Business.App app, IList<Zyg.SSO.Core.Business.AccountApp> newRelations)
        {
            DeleteUpdateAccountApps(null, app, newRelations);
        }

        public override void DeleteUpdateAccountApps(Zyg.SSO.Core.Business.Account account, Zyg.SSO.Core.Business.App app, IList<Zyg.SSO.Core.Business.AccountApp> newRelations)
        {
            MySqlUtility db = new MySqlUtility();

            StringBuilder sql = new StringBuilder();

            if (account != null && app == null)
            {
                db.AddParameter("@AccountID", MySqlDbType.VarChar, account.Id);

                sql.Append("Delete From `AccountApp` Where `AccountID` = @AccountID; ");

                int i = 0;

                foreach (AccountApp accountApp in newRelations)
                {
                    db.AddParameter("@ID" + i, MySqlDbType.VarChar, accountApp.Id);
                    db.AddParameter("@AppID" + i, MySqlDbType.VarChar, accountApp.AppID);
                    db.AddParameter("@DateCreated" + i, MySqlDbType.DateTime, accountApp.DateCreated);
                    db.AddParameter("@ExpireTime" + i, MySqlDbType.DateTime, accountApp.ExpireTime);

                    //sql.Append(string.Format("If NOT EXISTS (SELECT NULL FROM `AccountApp` WHERE `AccountID` = @AccountID AND `AppID` = @AppID{0}", i));
                    //sql.Append(" Begin ");
                    sql.Append("Insert Into `AccountApp` (`ID`, `AccountID`, `AppID`, `DateCreated`,`ExpireTime`) VALUES ( ");
                    sql.Append(String.Format("@ID{0}, @AccountID, @AppID{0}, @DateCreated{0}, @ExpireTime{0});", i));
                    //sql.Append(" END ");
                    i++;
                }
            }
            else if (account == null && app != null)
            {
                db.AddParameter("@AppID", MySqlDbType.VarChar, app.Id);

                sql.Append("Delete From `AccountApp` Where `AppID` = @AppID; ");

                int i = 0;

                foreach (AccountApp accountApp in newRelations)
                {
                    db.AddParameter("@ID" + i, MySqlDbType.VarChar, accountApp.Id);
                    db.AddParameter("@AccountID" + i, MySqlDbType.VarChar, accountApp.AppID);
                    db.AddParameter("@DateCreated" + i, MySqlDbType.DateTime, accountApp.DateCreated);
                    db.AddParameter("@ExpireTime" + i, MySqlDbType.DateTime, accountApp.ExpireTime);

                    //sql.Append(string.Format("If NOT EXISTS (SELECT NULL FROM `AccountApp` WHERE `AccountID` = @AccountID{0} AND `AppID` = @AppID ", i));
                    //sql.Append(" Begin ");
                    sql.Append("Insert Into `AccountApp` (`ID`, `AccountID`, `AppID`, `DateCreated`,`ExpireTime`) VALUES ( ");
                    sql.Append(String.Format("@ID{0}, @AccountID{0}, @AppID, @DateCreated{0}, @ExpireTime{0});", i));
                    //sql.Append(" END ");
                    i++;
                }
            }
            else
            {
                // Do nothing.
            }

            sql.Append(" set names utf8;");

            try
            {
                db.AutoCloseConnection = false;
                db.Connect();
                db.BeginTransaction();
                //db.Transaction.IsolationLevel = IsolationLevel.

                db.ExecuteSql(sql.ToString());
                db.CommitTransaction();
            }
            catch
            {
                db.RollbackTransaction();
            }
            finally
            {
                db.Disconnect();
            }
        }

        public override IList<Zyg.SSO.Core.Business.AccountApp> FillAccountApps(Zyg.SSO.Core.Business.Account account, Zyg.SSO.Core.Business.App app)
        {
            IList<AccountApp> results = new List<AccountApp>();

            MySqlUtility db = new MySqlUtility();
            MySqlDataReader reader;

            if (account != null && app == null)
            {
                db.AddParameter("@AccountID", MySqlDbType.VarChar, account.Id);
                reader = db.ExecuteSqlReader(SqlSelectAccountAppsByAccountId);
            }
            else if (account == null && app != null)
            {
                db.AddParameter("@AppID", MySqlDbType.VarChar, app.Id);
                reader = db.ExecuteSqlReader(SqlSelectAccountAppsByAppId);
            }
            else if (account != null && app != null)
            {
                db.AddParameter("@AccountID", MySqlDbType.VarChar, account.Id);
                db.AddParameter("@AppID", MySqlDbType.VarChar, app.Id);

                reader = db.ExecuteSqlReader(SqlSelectAccountAppByAccountIdAndAppId);
            }
            else
            {
                reader = null;
            }

            if (reader != null && !reader.IsClosed)
            {
                while (reader.Read())
                {
                    AccountApp accountApp = new AccountApp();

                    if (!reader.IsDBNull(0)) accountApp.Id = reader.GetGuid(0);
                    if (!reader.IsDBNull(1)) accountApp.AccountID = reader.GetGuid(1);
                    if (!reader.IsDBNull(2)) accountApp.AppID = reader.GetGuid(2);
                    if (!reader.IsDBNull(3)) accountApp.DateCreated = reader.GetDateTime(3);
                    if (!reader.IsDBNull(4)) accountApp.ExpireTime = reader.GetDateTime(4);

                    accountApp.MarkOld();

                    results.Add(accountApp);
                }

                reader.Close();
            }

            return results;
        }
    }
}
